package com.xj.server;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xj.dao.RecordDao;
import com.xj.util.Bean2XmlUtils;
import com.xj.util.ExcelUtils;
import com.xj.util.FileUtils;
import com.xj.util.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Slf4j
@Service
//@EnableScheduling
public class ReadfileService {
    @Value("${filepath}")
    private String filepath;

    @Autowired
    RecordDao recordDao;
    @Autowired
    NamedParameterJdbcTemplate jdbcTemplate;


    //   @Scheduled(cron = "${scheduledTask}")
    public void redaFile() throws Exception {
        File fileFold = new File(filepath);
        readFile(fileFold);

    }


    public void readFile(File fold) throws IOException {
        File[] files = fold.listFiles();
        for (int i = 0; i < files.length; i++) {
            File file = files[i];
            if (file.isDirectory()) {
                readFile(file);
            } else {
                if (file.getName().toLowerCase().endsWith(".xls") || file.getName().endsWith(".xlsx")) {
                    JSONArray jsonArray = ExcelUtils.reasExcelNoRepeat(file.getAbsolutePath(), 0);
                    this.recordDao.deleteAllByFilename(file.getName());
                    boolean result = dowithData(jsonArray, file.getName());
                    if (result) {
                        File destFile = new File(filepath.replace("todo", "success"));
                        if (!destFile.isDirectory()) {
                            destFile.mkdirs();
                        }
                        FileUtils.copyFileToDirectory(file, destFile);
                        file.delete();
                    } else {
                        File destFile = new File(filepath.replace("todo", "failure"));
                        if (!destFile.isDirectory()) {
                            destFile.mkdirs();
                        }
                        FileUtils.copyFileToDirectory(file, destFile);
                        file.delete();
                    }
                } else {
                    File destFile = new File(filepath.replace("todo", "failure"));
                    FileUtils.copyFileToDirectory(file, destFile);
                    file.delete();
                }
            }
        }
    }

    public boolean dowithData(JSONArray jsonArray, String filename) {
        boolean isSuccess = true;
        try {
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject = jsonArray.getJSONObject(i);
                List<String> fieldList = this.recordDao.getTableColumn("record");
                StringBuffer sb = new StringBuffer();
                Map map = new HashMap();
                StringBuffer str1 = new StringBuffer();
                str1.append("insert into  record(filename,no,createtime");
                StringBuffer str2 = new StringBuffer();
                str2.append("  values(:filename,:no,:createtime");
                for (String key : jsonObject.keySet()) {
                    key = key.replace("(", "").replace(")", "").replace("%", "")
                            .replace("-", "").replace("●", "").replace("★", "").trim();
                    String value = jsonObject.getString(key);
                    map.put("filename", filename);
                    map.put("no", i);
                    map.put("createtime", new java.util.Date());
                    if (fieldList.contains(key)) {
                        map.put(key, value);
                        str1.append(",").append(key);
                        str2.append(",").append(":").append(key);
                    } else if (StringUtils.isNotEmpty(value)) {
                        String sql = "alter  table  record   add " + key + "  varchar(100)  default ''";
                        this.jdbcTemplate.getJdbcOperations().update(sql);
                        map.put(key, value);
                        str1.append(",").append(key);
                        str2.append(",").append(":").append(key);
                    }
                }
                str1.append(")");
                str2.append(")");
                this.jdbcTemplate.update(str1.toString() + str2.toString(), map);
            }
        } catch (Exception err) {
            err.printStackTrace();
            isSuccess = false;
        }
        return isSuccess;
    }


    //   @Scheduled(cron = "0 45 1,11 ? * * ")
    public void outputFile() throws Exception {
        //  List<Record>  list=this.recordDao.findAll();
        //   XmlEntityVo xmlEntityVo=new XmlEntityVo(list);
        List<Map<String, Object>> list = this.recordDao.executeSql("select *  from   record");
        //    List<Map<String,Object>>  list=this.jdbcTemplate.getJdbcOperations().queryForList("select *  from record");


        Bean2XmlUtils.saxToXml(filepath, list);


    }


}
